﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
//using Oracle.DataAccess.Client;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using trangbaocao.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;
using System.Data;
namespace trangbaocao.Models
{

    public class Device_StationsModel
    {
        public int id { get; set; }
        [Required(ErrorMessage = "Tên thiết bị không được để trống!")]
        public string name { get; set; }
        [Required(ErrorMessage = "Mã thiết bị không được để trống!")]
        public string ma_he_thong { get; set; }
        [Required(ErrorMessage = "Nhà trạm không được để trống!")]
        public int stations_id { get; set; }
        public SelectList tram_id { get; set; }

        public int device_id { get; set; }
        public SelectList thietbi_id { get; set; }

        public int sort_device_id { get; set; }
        public int main_sort_device_id { get; set; }
        public int main_stations_id { get; set; }
        public SelectList trungtam_id { get; set; }
        public int manufactorys_id { get; set; }
        public string so_hieu_tb { get; set; }

        [Required(ErrorMessage = "Ngày lắp đặt không được để trống!")]
        public string ngay_lap_dat { get; set; }
        public string thong_tin_tb { get; set; }
        public int trang_thai_hd { get; set; }
        public string nv_lap_dat { get; set; }
        [Required(ErrorMessage = "Ngày sử dụng không được để trống!")]
        public string ngay_su_dung { get; set; }
        public string ghi_chu { get; set; }

        DeviceModel DeviceModel = new DeviceModel();
        StationsModel StationsModel = new StationsModel();
        Main_StationsModel Main_StationsModel = new Main_StationsModel();

        public List<Device_StationsModel> getDevice_Stations()
        {
            List<Device_StationsModel> Device_Stations = new List<Device_StationsModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM Device_Stations order by name");
            while (odr.Read())
            {
                Device_Stations.Add(new Device_StationsModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), stations_id = Convert.ToInt32(odr[2].ToString()), device_id = Convert.ToInt32(odr[3].ToString()), main_stations_id = Convert.ToInt32(odr[6].ToString()), so_hieu_tb = odr[8].ToString(), ngay_lap_dat = odr.IsDBNull(9) ? null : odr.GetDateTime(9).ToString("dd/MM/yyyy"),
                    thong_tin_tb = odr[10].ToString(), trang_thai_hd = Convert.ToInt32(odr[11].ToString()), nv_lap_dat = odr[12].ToString(), ngay_su_dung = odr.IsDBNull(13) ? null : odr.GetDateTime(13).ToString("dd/MM/yyyy"), ghi_chu = odr[14].ToString(),ma_he_thong=odr["ma_he_thong"].ToString() });
            }
            return Device_Stations;
        }
        public List<Main_StationsModel> getMain_StationsById(int id)
        {
            List<Main_StationsModel> Main_Stations = new List<Main_StationsModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * from Main_Stations where id="+id+" order by name");
            while (odr.Read())
            {
                Main_Stations.Add(new Main_StationsModel {id = Convert.ToInt32(odr[0].ToString()),name= odr[1].ToString(), dia_chi= odr[2].ToString(), quan_ly_tt= odr[3].ToString(), so_dien_thoai= odr[4].ToString(), ghi_chu= odr[5].ToString() });
            }
            return Main_Stations;
        }
        public List<SelectListItem> getStations(string MainStationId,string Order="name")
        {
            List<SelectListItem> list = new List<SelectListItem>() ;
            if (Convert.ToInt32(MainStationId) >= 0)
            {
                string sql = "select * from stations where main_stations_id=" +Convert.ToInt32(MainStationId)+" order by "+Order;
                OracleDataReader odr = General.Lib_GetDataReader(sql);                
                while (odr.Read())
                {
                    list.Add(new SelectListItem { Text = odr[1].ToString(), Value = odr[0].ToString() });                    
                }
            }
            return list;
        }
        public void addDevice_Stations(Device_StationsModel Device_Stations)
        {
            DeviceModel device = DeviceModel.getDevice().Find(delegate(DeviceModel d) { return d.id == Device_Stations.device_id; });
            StationsModel stations = StationsModel.getStations().Find(delegate(StationsModel s) { return s.id == Device_Stations.stations_id; });

            string sql = "INSERT INTO Device_Stations";
            //sql += " select nvl((select max(id)+1 from Device_Stations),1),'" + Device_Stations.name + "'," + Device_Stations.stations_id + "," + Device_Stations.device_id + "," + device.sort_device_id + "," + device.main_sort_device_id + "," + stations.main_stations_id + "," + device.manufactorys_id + ",'" + Device_Stations.so_hieu_tb + "',to_date('" + Device_Stations.ngay_lap_dat + "','dd/mm/yyyy'),'" + Device_Stations.thong_tin_tb + "'," + Device_Stations.trang_thai_hd + ",'" + Device_Stations.nv_lap_dat + "',to_date('" + Device_Stations.ngay_su_dung + "','dd/mm/yyyy'),'" + Device_Stations.ghi_chu + "','"+Device_Stations.ma_he_thong+"' from dual";
            sql += " select nvl((select max(id)+1 from Device_Stations),1),'" + Device_Stations.name + "'," + Device_Stations.stations_id + "," + Device_Stations.device_id + "," + device.sort_device_id + "," + device.main_sort_device_id + "," + stations.main_stations_id + "," + device.manufactorys_id + ",null,to_date('" + Device_Stations.ngay_lap_dat + "','dd/mm/yyyy'),null," + Device_Stations.trang_thai_hd + ",'" + Device_Stations.nv_lap_dat + "',to_date('" + Device_Stations.ngay_su_dung + "','dd/mm/yyyy'),'" + Device_Stations.ghi_chu + "','" + Device_Stations.ma_he_thong + "' from dual";
            General.Lib_ExecuteNonQuery(sql);
        }
        public void updateDevice_Stations(Device_StationsModel Device_Stations)
        {
            DeviceModel device = DeviceModel.getDevice().Find(delegate(DeviceModel d) { return d.id == Device_Stations.device_id; });
            StationsModel stations = StationsModel.getStations().Find(delegate(StationsModel s) { return s.id == Device_Stations.stations_id; });

            string sql = "UPDATE Device_Stations SET name='" + Device_Stations.name + "', stations_id=" + Device_Stations.stations_id + ",device_id=" + Device_Stations.device_id + ",sort_device_id=" + device.sort_device_id + ",main_sort_device_id=" + device.main_sort_device_id + ",main_stations_id=" + stations.main_stations_id + ",manufactorys_id=" + device.manufactorys_id + ",ngay_lap_dat=to_date('" + Device_Stations.ngay_lap_dat + "','dd/mm/yyyy'),trang_thai_hd=" + Device_Stations.trang_thai_hd + ",nv_lap_dat='" + Device_Stations.nv_lap_dat + "',ngay_su_dung=to_date('" + Device_Stations.ngay_su_dung + "','dd/mm/yyyy'),ghi_chu='" + Device_Stations.ghi_chu + "',ma_he_thong='" + Device_Stations.ma_he_thong + "' where id=" + Device_Stations.id;
            //string sql = "UPDATE Device_Stations SET name='" + Device_Stations.name + "', stations_id=" + Device_Stations.stations_id + ",device_id=" + Device_Stations.device_id + ",sort_device_id=" + device.sort_device_id + ",main_sort_device_id=" + device.main_sort_device_id + ",main_stations_id=" + stations.main_stations_id + ",manufactorys_id=" + device.manufactorys_id + ",so_hieu_tb='" + Device_Stations.so_hieu_tb + "',ngay_lap_dat=to_date('" + Device_Stations.ngay_lap_dat + "','dd/mm/yyyy'),thong_tin_tb='" + Device_Stations.thong_tin_tb + "',trang_thai_hd=" + Device_Stations.trang_thai_hd + ",nv_lap_dat='" + Device_Stations.nv_lap_dat + "',ngay_su_dung=to_date('" + Device_Stations.ngay_su_dung + "','dd/mm/yyyy'),ghi_chu='" + Device_Stations.ghi_chu + "',ma_he_thong='"+Device_Stations.ma_he_thong+"' where id=" + Device_Stations.id;
            
            General.Lib_ExecuteNonQuery(sql);
        }
        public void deleteDevice_Stations(int id)
        {
            string sql = "DELETE FROM Device_Stations WHERE id=" + id;
            General.Lib_ExecuteNonQuery(sql);
        }

        public int checkSubId_Fuel(int id)
        {
            string sql = "SELECT *  FROM Fuel WHERE device_stations_id='" + id + "'";
            return General.Lib_CheckExist(sql);
        }
        public int checkSubId_Fuel_Detail(int id)
        {
            string sql = "SELECT *  FROM Fuel_Detail WHERE device_stations_id='" + id + "'";
            return General.Lib_CheckExist(sql);
        }
        public int checkExist(Device_StationsModel Device_Stations)
        {
            string sql = "SELECT * FROM Device_Stations WHERE name='" + Device_Stations.name + "'";
            return General.Lib_CheckExist(sql);
        }
        public int checkExist_Ma_he_thong(Device_StationsModel Device_Stations)
        {
            string sql = "SELECT * FROM Device_Stations WHERE ma_he_thong='" + Device_Stations.ma_he_thong + "'";
            return General.Lib_CheckExist(sql);
        }
        public List<Device_StationsModel> FindDevice_Stations(string str)
        {
            List<Device_StationsModel> Device_Stations = new List<Device_StationsModel>();
            OracleDataReader odr = General.Lib_GetDataReader("SELECT * FROM Device_Stations WHERE " + str);
            while (odr.Read())
            {
                Device_Stations.Add(new Device_StationsModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), stations_id = Convert.ToInt32(odr[2].ToString()), device_id = Convert.ToInt32(odr[3].ToString()), so_hieu_tb = odr[8].ToString(), ngay_lap_dat = odr[9].ToString(), thong_tin_tb = odr[10].ToString(), trang_thai_hd = Convert.ToInt32(odr[11].ToString()), nv_lap_dat = odr[12].ToString(), ngay_su_dung = odr[13].ToString(), ghi_chu = odr[14].ToString() });
            }
            return Device_Stations;
        }
        //public DataSet bc_Tongthietbi()
        //{
        //    string sql = "SELECT a.name,a.ghi_chu, a.tong_so_loai_tb, a.tong_so_tb, a.tong_so_tb_hien_co FROM tong_so_tb a";
        //    OracleConnection conn = General.ConnectDataBase();
        //    OracleCommand omd = new OracleCommand(sql, conn);
        //    OracleDataAdapter oda = new OracleDataAdapter(omd);
        //    DataSet ds = new DataSet();
        //    try
        //    {
        //        conn.Open();
        //    }
        //    catch
        //    {
        //        System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Lỗi kết nối với cơ sở dữ liệu!')</SCRIPT>");
        //    }
        //    oda.Fill(ds, "0");
        //    conn.Close();
        //    return ds;
        //}
        public void move_thietbi(Move_Device mv_dv)
        {
            string sql = "INSERT INTO MOVE_DEVICE";
            sql += " select nvl((select max(id)+1 from MOVE_DEVICE),1), " + mv_dv.device_stations_id +", "+ mv_dv.stations_id_from + "," + mv_dv.stations_id_to + ",to_date('" + mv_dv.date_move + "','dd/mm/yyyy'), '" + mv_dv.note_move + "', '" + mv_dv.nv_dichchuyen + "', '" + mv_dv.ma_ht_old + "','" + mv_dv.ma_ht_new + "','" + mv_dv.name_tb_old + "', '" + mv_dv.name_tb_new + "' from dual";
            General.Lib_ExecuteNonQuery(sql);
        }
        public List<Move_Device> getListLichsu(int device_station_id)
        { 
            string sql = "SELECT a.ten_tb_old, a.ma_ht_old, a.ten_tb_new, a.ma_ht_new, b.stations_name_from, b.main_stations_name, d.stations_name_to, d.main_stations_name, a.nv_dichchuyen, to_char(a.date_move,'dd/mm/yyyy'), a.note  FROM MOVE_DEVICE a, ";
            sql += " (SELECT a.id, a.stations_id_from stations_id_from, b.name stations_name_from, c.name main_stations_name  from move_device a, stations b, main_stations c where a.stations_id_from = b.id and b.main_stations_id=c.id) b, ";
                   sql+=" (SELECT a.id, a.stations_id_to stations_id_to, b.name stations_name_to, c.name main_stations_name  from move_device a, stations b, main_stations c where a.stations_id_to = b.id and b.main_stations_id=c.id) d "; 
                   sql+=" where a.id = b.id and a.id=d.id and a.device_stations_id=" + device_station_id + " order by a.id";
            List<Move_Device> listMove_Device = new List<Move_Device>();
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                listMove_Device.Add(new Move_Device 
                { 
                  name_tb_old = odr[0].ToString(),
                  ma_ht_old = odr[1].ToString(),
                  name_tb_new = odr[2].ToString(),
                  ma_ht_new = odr[3].ToString(),
                  stations_name_from = odr[4].ToString(),
                  main_stations_name_from=odr[5].ToString(),
                  stations_name_to = odr[6].ToString(),
                  main_stations_name_to = odr[7].ToString(),
                  nv_dichchuyen = odr[8].ToString(),
                  date_move = odr[9].ToString(),
                  note_move = odr[10].ToString()
                });
            }
            return listMove_Device;
        }
        //add by namtd 07/03/2014
        public void updateFuel_and_FuelDetail(Device_StationsModel Device_Stations)
        {

            string sql = "UPDATE Fuel SET main_stations_id=" + Device_Stations.main_stations_id + ",stations_id=" + Device_Stations.stations_id + " where device_stations_id=" + Device_Stations.id;
            string sql1 = "UPDATE Fuel_Detail SET main_stations_id=" + Device_Stations.main_stations_id + ",stations_id=" + Device_Stations.stations_id + " where device_stations_id=" + Device_Stations.id;
            General.Lib_ExecuteNonQuery(sql);
            General.Lib_ExecuteNonQuery(sql1);
        }

    }
    public class Device_StationsAssociateModel
    {
        public int id { get; set; }
        public string name { get; set; }
        public string stations_name { get; set; }
        public string device_name { get; set; }
        public string main_stations_name { get; set; }
        public string sort_device_name { get; set; }
        public string main_sort_device_name { get; set; }
        //public int sort_device_name { get; set; }
        //public int main_sort_device_name { get; set; }
        //public int main_stations_id { get; set; }
        //public int manufactorys_name { get; set; }
        //public SelectList trungtam_id { get; set; }

        public string so_hieu_tb { get; set; }
        public string ngay_lap_dat { get; set; }
        public string thong_tin_tb { get; set; }
        public string trang_thai_hd { get; set; }
        public string nv_lap_dat { get; set; }
        public string ngay_su_dung { get; set; }
        public string ghi_chu { get; set; }
        public string ma_he_thong { get; set; }
        public List<Device_StationsAssociateModel> getDevice_StationsIndex()
        {
            List<Device_StationsAssociateModel> Device_Stations = new List<Device_StationsAssociateModel>();
            string sql="";
            if (HttpContext.Current.Session != null)
            {
                int quyen_id = Convert.ToInt16(HttpContext.Current.Session["quyen_id"]);
                int trungtam_id = Convert.ToInt16(HttpContext.Current.Session["trungtam_id"]);
                if (quyen_id > 2)
                    sql = "SELECT a.id, a.name, c.name,e.name,f.name, b.name,d.name, a.so_hieu_tb, a.ngay_lap_dat, a.thong_tin_tb, a.trang_thai_hd,a.nv_lap_dat, a.ngay_su_dung, a.ghi_chu,a.ma_he_thong FROM Device_Stations a,stations b,device c,main_stations d,sort_device e,main_sort_device f where a.stations_id=b.id and a.device_id=c.id and a.main_stations_id=d.id and a.sort_device_id=e.id and a.main_sort_device_id=f.id and d.id=" + trungtam_id + "  order by d.name,b.name,f.name,e.name,c.name,a.name";
                else
                    sql = "SELECT a.id, a.name, c.name,e.name,f.name, b.name,d.name, a.so_hieu_tb, a.ngay_lap_dat, a.thong_tin_tb, a.trang_thai_hd,a.nv_lap_dat, a.ngay_su_dung, a.ghi_chu,a.ma_he_thong FROM Device_Stations a,stations b,device c,main_stations d,sort_device e,main_sort_device f where a.stations_id=b.id and a.device_id=c.id and a.main_stations_id=d.id and a.sort_device_id=e.id and a.main_sort_device_id=f.id order by d.name,b.name,f.name,e.name,c.name,a.name";
            }
            else
                sql = "SELECT a.id, a.name, c.name,e.name,f.name, b.name,d.name, a.so_hieu_tb, a.ngay_lap_dat, a.thong_tin_tb, a.trang_thai_hd,a.nv_lap_dat, a.ngay_su_dung, a.ghi_chu,a.ma_he_thong FROM Device_Stations a,stations b,device c,main_stations d,sort_device e,main_sort_device f where a.stations_id=b.id and a.device_id=c.id and a.main_stations_id=d.id and a.sort_device_id=e.id and a.main_sort_device_id=f.id order by d.name,b.name,f.name,e.name,c.name,a.name";
            
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                Device_Stations.Add(new Device_StationsAssociateModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), device_name = odr[2].ToString(), sort_device_name = odr[3].ToString(), main_sort_device_name = odr[4].ToString(), stations_name = odr[5].ToString(), main_stations_name = odr[6].ToString(), so_hieu_tb = odr[7].ToString(), ngay_lap_dat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"), thong_tin_tb = odr[9].ToString(), trang_thai_hd = Convert.ToInt32(odr[10].ToString()) == 0 ? "Ngừng" : "Bình thường", nv_lap_dat = odr[11].ToString(), ngay_su_dung = odr.IsDBNull(12) ? null : odr.GetDateTime(12).ToString("dd/MM/yyyy"), ghi_chu = odr[13].ToString(), ma_he_thong = odr["ma_he_thong"].ToString() });
            }
            return Device_Stations;
        }
        public List<Device_StationsAssociateModel> FindDevice_Stations(string str)
        {
            List<Device_StationsAssociateModel> Device_Stations = new List<Device_StationsAssociateModel>();
            string sql = "SELECT a.id, a.name, c.name,e.name,f.name, b.name,d.name, a.so_hieu_tb, a.ngay_lap_dat, a.thong_tin_tb, a.trang_thai_hd,a.nv_lap_dat, a.ngay_su_dung, a.ghi_chu,a.ma_he_thong FROM Device_Stations a,stations b,device c,main_stations d,sort_device e,main_sort_device f where a.stations_id=b.id and a.device_id=c.id and a.main_stations_id=d.id and a.sort_device_id=e.id and a.main_sort_device_id=f.id AND " + str + " order by d.name,b.name,f.name,e.name,c.name,a.name";
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            
            while (odr.Read())
            {
                Device_Stations.Add(new Device_StationsAssociateModel { id = Convert.ToInt32(odr[0].ToString()), name = odr[1].ToString(), device_name = odr[2].ToString(), sort_device_name = odr[3].ToString(), main_sort_device_name = odr[4].ToString(), stations_name = odr[5].ToString(), main_stations_name = odr[6].ToString(), so_hieu_tb = odr[7].ToString(), ngay_lap_dat = odr.IsDBNull(8) ? null : odr.GetDateTime(8).ToString("dd/MM/yyyy"), thong_tin_tb = odr[9].ToString(), trang_thai_hd = Convert.ToInt32(odr[10].ToString()) == 0 ? "Ngừng" : "Bình thường", nv_lap_dat = odr[11].ToString(), ngay_su_dung = odr.IsDBNull(12) ? null : odr.GetDateTime(12).ToString("dd/MM/yyyy"), ghi_chu = odr[13].ToString(), ma_he_thong = odr["ma_he_thong"].ToString() });
            }
            return Device_Stations;
        }
        
    }
    public class Move_Device
    {
        public int id { get; set; }
        public int device_stations_id { get; set; }
        public int stations_id_from { get; set; }
        public string stations_name_from {get; set;}
        public int stations_id_to { get; set; }
        public string main_stations_name_from { get; set; }
        public string main_stations_name_to { get; set; }
        public string stations_name_to { get; set; }
        public string date_move { get; set; }
        public string note_move { get; set; }
        public string nv_dichchuyen { get; set; }
        public string ma_ht_old { get; set; }
        public string ma_ht_new { get; set; }
        public string name_tb_old { get; set; }
        public string name_tb_new { get; set; }
    }
}